package com.HTMLParsers;

import java.net.URLEncoder;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.nodes.Entities.EscapeMode;
import org.jsoup.select.Elements;

import DBPack.DBStatement;

public class StationParser_v2 {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		try {
      	  int j=0;
      	  String query="SELECT * FROM transportation.routes" ;
			  PreparedStatement ps=(PreparedStatement) DBStatement.getMainConnection().prepareStatement(query);
			  ResultSet set = ps.executeQuery();
			  while (set.next()) {
				  String routeCode=set.getString("route_code");
				  String routeName=URLEncoder.encode(set.getString("route_name"));
				  j++;
				  getAndInsertStationsOfRoute(routeCode);  
			  }	  
			  
			 
			  
        } catch(SQLException e) {
      	  
        } catch(Exception e) {
      	  
        }
	}	
	public static void getAndInsertStationsOfRoute(String routeCode) {
		try  {
		String url = "http://mobil.iett.gov.tr/hatdetay.php?hatcode="+routeCode;
	    //Document doc = Jsoup.parse(input, "iso-8859-9", url);
	    Document doc = Jsoup.connect(url).timeout(100000).get();
	    doc.outputSettings().charset("UTF-8");
	    doc.outputSettings().escapeMode(EscapeMode.xhtml);
	    Element content = doc.select("table[width=500] tr").first();
	    if (content==null || content.equals(""))
	    	return;
	    
	    Elements tables=content.select("td[valign=top]");
	    
	    if (tables==null || tables.toString().equals(""))
	    	return;
	    
		String stationCode;
		String stationName;
		int lastIndexOfSpace;
		Elements stations;
		Element tr;
		Element element;
	    for (int i=0;i<tables.size();i++) {
	    	element=tables.get(i);
	    	if (element!=null && !element.html().equals("")) {
	    		
	    		stations=element.getElementsByTag("table").first().getElementsByTag("tr");
	    		for(int j=0;j<stations.size();j++) {
	    			tr=stations.get(j);
	    		    stationCode=tr.getElementsByTag("a").first().attr("href").split("&")[1].split("=")[1].trim();
	    			stationName=tr.select("a").text();
	    			lastIndexOfSpace=stationName.trim().lastIndexOf(" ");
	    			System.out.println(stationCode+" - "+stationName.substring(0, lastIndexOfSpace)+" - "+stationName.substring(lastIndexOfSpace, stationName.length()));
	    			insertStation(stationCode,stationName.substring(0, lastIndexOfSpace),stationName.substring(lastIndexOfSpace, stationName.length()));
	    			//insertIntoStationDetails(stationCode,routeCode,routeDirection,stationOrder);
	    			//System.out.println(routeCode+"- "+stationOrder+" - "+stationCode+" - "+stationName+" - "+"- "+routeDirection);
	    		} 		
	    		//insertIntoStationDetails(stationCode,routeCode);
	    	}
	    }
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
		}
	}
	public static void insertStation(String stationCode,String stationName,String stationLocation) {
		try{
			String query="INSERT INTO transportation.stations(station_code,station_name,station_location) VALUES (?, ?, ?)";
			PreparedStatement ps=(PreparedStatement) DBStatement.getMainConnection().prepareStatement(query);
			ps.setString(1, stationCode);
			ps.setString(2, stationName);
			ps.setString(3, stationLocation);
			ps.executeUpdate();
		} catch(SQLException e) {
			System.err.println("SQLException: "+e.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getMessage());
		}
		
	}
	
	

}
